/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sistemperpustakaan.model;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
import javax.sound.midi.SysexMessage;
import javax.swing.JOptionPane;
import sistemperpustakaan.entity.Inventaris;
import sistemperpustakaan.entity.ViewInventaris;
import sistemperpustakaan.utility.DbUtility;

/**
 *
 * @author EKo
 */
public class InventarisModel {

    Connection connection;
    List<ViewInventaris> listInventaris;
    Inventaris inventaris;
    ViewInventaris lI;
    Vector vectorPenerbit;

    public InventarisModel() {
        connection = new DbUtility().getConnection();
    }

    public Connection getConnection() {
        return connection;
    }

    public Vector getVectorPenerbit() {
        vectorPenerbit = new Vector();
        String query = "SELECT nama_penerbit from penerbit ORDER BY id";
        Statement statement = null;
        try {
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            vectorPenerbit.add("");
            while (rs.next()) {
                vectorPenerbit.add(rs.getString(1));
            }
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error when retrieve data" + e.getMessage());
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return vectorPenerbit;
    }

    public List<ViewInventaris> getListInventaris(String inventaris_id, String buku_id, String no_panggil, String isbn, String judul, String pengarang, String penerbit, int status) {
        listInventaris = new ArrayList<ViewInventaris>();
        CallableStatement statement = null;
        ViewInventaris viewInventaris;
        try {
            statement = connection.prepareCall("CALL pCariInventaris ('" + inventaris_id + "', '" + buku_id + "','" + no_panggil + "', '" + isbn + "','" + judul + "', '" + pengarang + "','" + penerbit + "'," + status+")");
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                viewInventaris = new ViewInventaris(rs.getString(1), rs.getInt(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));
                listInventaris.add(viewInventaris);
            }
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error when retrieve data" + e.getMessage());
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return listInventaris;
    }

    public ViewInventaris getViewInventaris(String id) {
        String query = "SELECT * FROM vInventaris WHERE id_inventaris= '"+id+"' AND status='Tersedia'";
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(query);
            //statement.setString(1, id);
            ResultSet rs = statement.executeQuery();
            if(rs.next()){
                lI = new ViewInventaris(rs.getString(1), rs.getInt(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));

            }
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Error when retrieve data buku"+ex.getMessage());
        }finally{try {if(statement != null)statement.close(); } catch (Exception e) { }}
        return lI;
    }

    public Inventaris getInventaris(String id) {
        String query = "SELECT * FROM inventaris WHERE id= '"+id+"'";
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(query);
            //statement.setString(1, id);
            ResultSet rs = statement.executeQuery();
            if(rs.next()){
                inventaris  = new Inventaris(rs.getString(1), rs.getInt(2), rs.getInt(3));

            }
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Error when retrieve data buku"+ex.getMessage());
        }finally{try {if(statement != null)statement.close(); } catch (Exception e) { }}
        return inventaris;
    }

    public boolean deleteInventaris(String id){
        String query = "DELETE FROM INVENTARIS WHERE id= '"+id+"'";
        PreparedStatement statement=null;
        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(query);
            //statement.setString(1, id);
            if(statement.executeUpdate()==1) connection.commit();
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Error when delete data\n Error: "+ex.getMessage());
            try { connection.rollback(); } catch (SQLException exr) {}
            return false;
        }finally{try {if(statement != null)statement.close(); } catch (Exception e) { }}
        return true;
    }

    public boolean insertInventaris(String idInventaris, String idBuku, int status) {
        PreparedStatement statement= null;
        String query = "INSERT INTO Inventaris VALUES('" + idInventaris + "','"+idBuku + "',"+status+")";
        try {
            connection.setAutoCommit(false);
            //statement.setString(1, kategori);
            System.out.println(query);
            if(statement.executeUpdate() == 1) connection.commit();
        } catch (Exception e) {
            try { connection.rollback();statement.close();} catch (Exception ex) { }
            JOptionPane.showMessageDialog(null, "Error when insert data"+e.getMessage());
            return false;
        }finally{try {if(statement != null)statement.close(); } catch (Exception e) { }}
        return true;
    }

     public boolean updateInventaris(String idInventaris, String idBuku, int status, String id_lama) {
        PreparedStatement statement= null;
        try {
            connection.setAutoCommit(false);
            String query= "UPDATE inventaris SET id='" + idInventaris +"',buku_id='"+idBuku+"', status="+status+" WHERE id='"+id_lama+"'";
            statement = connection.prepareStatement(query);
            //statement.setString(1, kategori);
            //statement.setInt(2, id);
            if(statement.executeUpdate() == 1) connection.commit();
                //System.out.println(query);
        } catch (Exception e) {
            try { connection.rollback();statement.close();} catch (Exception ex) { }
            JOptionPane.showMessageDialog(null, "Error when update data"+e.getMessage());
            return false;
        }finally{try {if(statement != null)statement.close(); } catch (Exception e) { }}
        return true;
    }

     public Inventaris generateIdInventaris() {
        PreparedStatement statement = null;
        Inventaris inventaris = null;
        try {
            statement = connection.prepareStatement("SELECT fGenerateIdInventaris() AS Kode");
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                inventaris = new Inventaris(rs.getString(1), 0, 0);
            }
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error when retrieve data" + e.getMessage());
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return inventaris;
    }
}
